package com.powergisol.gis.listener;
import	java.util.List;
import	java.util.ArrayList;

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.powergisol.gis.entity.*;
import com.powergisol.gis.service.SaveDataService;
import com.powergisol.gis.util.XyChangeUtil;
import com.powergisol.gis.vo.ExcelInFailedDataVo;
import com.powergisol.gis.vo.SzZkCmdStandardVo;
import com.powergisol.gis.vo.TaskResultVo;
import org.springframework.cache.Cache;
import org.springframework.cache.CacheManager;

import java.math.BigDecimal;

/**
 * Description:excel读取监听器
 */
public class ExcelModelListener extends AnalysisEventListener {

    SzZkCmdStandardVo vo;

    SaveDataService saveDataService;

    CacheManager cacheManager;

    Integer settingId;

    TabCoordinateSystem system;

    public ExcelModelListener() {
    }

    public ExcelModelListener(SzZkCmdStandardVo vo,SaveDataService saveDataService, Integer settingId,
                                 CacheManager cacheManager, TabCoordinateSystem system) {
        this.vo = vo;
        this.saveDataService = saveDataService;
        this.settingId = settingId;
        this.cacheManager = cacheManager;
        this.system = system;
    }

    /**
     * 每隔1000条存储数据库，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 1000;

    List<Object> list = new ArrayList<>();

    int i = 0;//循环次数

    int listSize = 0;//总的条数

    StringBuffer projects = new StringBuffer();//工程

    ArrayList titleData;


    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        if(null != vo.getReadRows()){
            if(listSize<=vo.getReadRows()-1){
                list.add(o);
                if (list.size() >= BATCH_COUNT) {
                    i = i + 1;
                    saveData();
                    list.clear();
                }
            }
        }else{
            list.add(o);
            if (list.size() >= BATCH_COUNT) {
                i = i + 1;
                saveData();
                list.clear();
            }
        }
        if(vo.getTitleRows()-1 == listSize){
            titleData = (ArrayList) o;
        }
        listSize = listSize +1;
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        i = i + 1;
        saveData();
        System.out.println("解析完成");
    }

    /**
     * 加上存储数据库
     */
    public void saveData() {
        List<TabZuankong> listZk = new ArrayList<>();//存放钻孔数据
        List<TabZkTuceng> listTc = new ArrayList<>();//存放土层数据
        List<TabZkShuiwei> listSw = new ArrayList<>();//存放水位数据
        List<TabProject> listP = new ArrayList<>();//存放项目数据
        List<ExcelInFailedDataVo> listFailed = new ArrayList<>();//失败数据
        String cellValue = null;
        Integer startRows = 0;//开始行数
        Integer rowNums = 1;
        if (i == 1) {
            startRows = vo.getStartRows();
            rowNums = vo.getStartRows();
        } else {
            startRows = 1;
            rowNums = (i-1) * BATCH_COUNT + 1;
        }
        for (int rowNum = startRows-1; rowNum < list.size(); rowNum++) {
            TabZuankong zk = new TabZuankong();
            TabZkTuceng tc = new TabZkTuceng();
            TabZkShuiwei sw = new TabZkShuiwei();
            TabProject tabProject = new TabProject();
            ExcelInFailedDataVo failedDataVo = new ExcelInFailedDataVo();
            Object rowData = list.get(rowNum);
            ArrayList datas = (ArrayList) rowData;
            BigDecimal x = null;
            BigDecimal y = null;
            for (int colNum = 0; colNum < datas.size(); colNum++) {
                cellValue = String.valueOf(datas.get(colNum));
                //判断值是否为空
                boolean flag = (null == cellValue || ("").equals(cellValue)||("null").equals(cellValue));
//                if (null == cellValue || ("").equals(cellValue)||("null").equals(cellValue)) {
//                    continue;
//                }
                //钻孔值设置
                if (!flag && null != vo.getZKBH() && colNum + 1 == vo.getZKBH()) {
                    zk.setZKBH(cellValue);
                    tc.setZKBH(cellValue);
                    sw.setZKBH(cellValue);
                    continue;
                }
                if (!flag && null != vo.getZKLX() && colNum + 1 == vo.getZKLX()) {
                    zk.setZKLX(cellValue);
                    continue;
                }
                if (colNum + 1 == vo.getZKX()) {
                    if (flag || !isNumber(cellValue)) {
                        failedDataVo.setReason(titleData.get(colNum)+"需为数字");
                       // listFailed.add(failedDataVo);
                       // rowNums++;
                        break;
                    }
                    x = new BigDecimal(cellValue);
                    continue;
                }
                if (colNum + 1 == vo.getZKY()) {
                    if (flag || !isNumber(cellValue)) {
                        failedDataVo.setReason(titleData.get(colNum)+"需为数字");
                        //listFailed.add(failedDataVo);
                        break;
                    }
                    y = new BigDecimal(cellValue);
                    continue;
                }
                if (!flag && null != vo.getJZDD() && colNum + 1 == vo.getJZDD()) {
                    zk.setJZDD(cellValue);
                    continue;
                }
                if (!flag && null != vo.getZKDH() && colNum + 1 == vo.getZKDH()) {
                    if (isNumber(cellValue)) {
                        zk.setZKDH(new Double(cellValue).intValue());
                    }
                    continue;
                }
                if (!flag && null != vo.getZKLC() && colNum + 1 == vo.getZKLC()) {
                    if (isNumber(cellValue)) {
                        zk.setZKLC(new BigDecimal(cellValue));
                    }
                    continue;
                }
                if (!flag && null != vo.getZKPIL() && colNum + 1 == vo.getZKPIL()) {
                    if (isNumber(cellValue)) {
                        zk.setZKPIL(new BigDecimal(cellValue));
                    }
                    continue;
                }
                if (colNum + 1 == vo.getZKBG()) {
                    if (flag || !isNumber(cellValue)) {
                        failedDataVo.setReason(titleData.get(colNum)+"需为数字");
                        //listFailed.add(failedDataVo);
                       // rowNums++;
                        break;
                    }
                    zk.setZKBG(new BigDecimal(cellValue));
                    continue;
                }
                if (null != vo.getZKHSBG() && colNum + 1 == vo.getZKHSBG()) {
                    if (isNumber(cellValue)) {
                        zk.setZKHSBG(new BigDecimal(cellValue));
                    }
                    continue;
                }
                if (null != vo.getZKSD() && colNum + 1 == vo.getZKSD()) {
                    if (isNumber(cellValue)) {
                        zk.setZKSD(new BigDecimal(cellValue));
                    }
                    continue;
                }
                if (null != vo.getZKTJSD() && colNum + 1 == vo.getZKTJSD()) {
                    if (isNumber(cellValue)) {
                        zk.setZKTJSD(new BigDecimal(cellValue));
                    }
                    continue;
                }
                if (null != vo.getZKZJ() && colNum + 1 == vo.getZKZJ()) {
                    if (isNumber(cellValue)) {
                        zk.setZKZJ(new BigDecimal(cellValue));
                    }
                    continue;
                }
                if (!flag && null != vo.getZKDCLC() && colNum + 1 == vo.getZKDCLC()) {
                    zk.setZKDCLC(cellValue);
                    continue;
                }
                if (null != vo.getZKDCJJ() && colNum + 1 == vo.getZKDCJJ()) {
                    if (isNumber(cellValue)) {
                        zk.setZKDCJJ(new BigDecimal(cellValue));
                    }
                    continue;
                }
                if (null != vo.getZKDCQSD() && colNum + 1 == vo.getZKDCQSD()) {
                    if (isNumber(cellValue)) {
                        zk.setZKDCQSD(new BigDecimal(cellValue));
                    }
                    continue;
                }
                if (null != vo.getZKDCZSD() && colNum + 1 == vo.getZKDCZSD()) {
                    if (isNumber(cellValue)) {
                        zk.setZKDCZSD(new BigDecimal(cellValue));
                    }
                    continue;
                }
                if (!flag && null != vo.getZKDCPM() && colNum + 1 == vo.getZKDCPM()) {
                    zk.setZKDCPM(cellValue);
                    continue;
                }
                if (!flag && null != vo.getZKXN() && colNum + 1 == vo.getZKXN()) {
                    zk.setZKXN(cellValue);
                    continue;
                }
                if (!flag && null != vo.getZKSFCY() && colNum + 1 == vo.getZKSFCY()) {
                    zk.setZKSFCY(cellValue);
                    continue;
                }
                if (!flag && null != vo.getZKJYK() && colNum + 1 == vo.getZKJYK()) {
                    zk.setZKJYK(cellValue);
                    continue;
                }
                if (null != vo.getZKDXSW() && colNum + 1 == vo.getZKDXSW()) {
                    if (isNumber(cellValue)) {
                        zk.setZKDXSW(new BigDecimal(cellValue));
                    }
                    continue;
                }
                if (null != vo.getZKTCJD() && colNum + 1 == vo.getZKTCJD()) {
                    if (isNumber(cellValue)) {
                        zk.setZKTCJD(new BigDecimal(cellValue));
                    }
                    continue;
                }
                if (!flag && null != vo.getZKKSRQ() && colNum + 1 == vo.getZKKSRQ()) {
                    zk.setZKKSRQ(cellValue);
                    continue;
                }
                if (!flag && null != vo.getZKZZRQ() && colNum + 1 == vo.getZKZZRQ()) {
                    zk.setZKZZRQ(cellValue);
                    continue;
                }
                if (!flag && null != vo.getZKDJ() && colNum + 1 == vo.getZKDJ()) {
                    zk.setZKDJ(cellValue);
                    continue;
                }
                if (!flag && null != vo.getZKPZTSDXS() && colNum + 1 == vo.getZKPZTSDXS()) {
                    if (isNumber(cellValue)) {
                        zk.setZKPZTSDXS(new BigDecimal(cellValue));
                    }
                    continue;
                }
                if (null != vo.getZKPZDQYXSD() && colNum + 1 == vo.getZKPZDQYXSD()) {
                    if (isNumber(cellValue)) {
                        zk.setZKPZDQYXSD(new BigDecimal(cellValue));
                    }
                    continue;
                }
                if (!flag && null != vo.getBZ() && colNum + 1 == vo.getBZ()) {
                    zk.setBZ(cellValue);
                    continue;
                }
                if (!flag && null != vo.getZKH() && colNum + 1 == vo.getZKH()) {
                    zk.setZKH(cellValue);
                    continue;
                }
                if (!flag && null != vo.getZKSC() && colNum + 1 == vo.getZKSC()) {
                    zk.setZKSC(cellValue);
                    continue;
                }
                if (null != vo.getZKV() && colNum + 1 == vo.getZKV()) {
                    if (isNumber(cellValue)) {
                        zk.setZKV(new Double(cellValue).intValue());
                    }
                    continue;
                }
                if (null != vo.getZKYHZS() && colNum + 1 == vo.getZKYHZS()) {
                    if (isNumber(cellValue)) {
                        zk.setZKYHZS(new BigDecimal(cellValue));
                    }
                    continue;
                }
                if (null != vo.getSXDJ() && colNum + 1 == vo.getSXDJ()) {
                    if (isNumber(cellValue)) {
                        zk.setSXDJ(new Double(cellValue).intValue());
                    }
                    continue;
                }
                if (!flag && null != vo.getZKLX() && colNum + 1 == vo.getZKLX()) {
                    zk.setZKLX(cellValue);
                    continue;
                }
                if (null != vo.getZKPZQS() && colNum + 1 == vo.getZKPZQS()) {
                    if (isNumber(cellValue)) {
                        zk.setZKPZQS(new Double(cellValue).intValue());
                    }
                    continue;
                }
                if (null != vo.getZKPZDJ() && colNum + 1 == vo.getZKPZDJ()) {
                    if (isNumber(cellValue)) {
                        zk.setZKPZDJ(new Double(cellValue).intValue());
                    }
                    continue;
                }
                if (!flag && null != vo.getDTABLE() && colNum + 1 == vo.getDTABLE()) {
                    zk.setDTABLE(cellValue);
                    continue;
                }
                if (!flag && null != vo.getYSGCBH() && colNum + 1 == vo.getYSGCBH()) {
                    zk.setYSGCBH(cellValue);
                    continue;
                }
                if (null != vo.getZKCOLOR() && colNum + 1 == vo.getZKCOLOR()) {
                    if (isNumber(cellValue)) {
                        zk.setZKCOLOR(new Double(cellValue).intValue());
                    }
                    continue;
                }
                if (!flag && null != vo.getZKZBH() && colNum + 1 == vo.getZKZBH()) {
                    zk.setZKZBH(cellValue);
                    continue;
                }
                if (!flag && null != vo.getZKSZFF() && colNum + 1 == vo.getZKSZFF()) {
                    zk.setZKSZFF(cellValue);
                    continue;
                }
                if (!flag && null != vo.getZKZJLX() && colNum + 1 == vo.getZKZJLX()) {
                    zk.setZKZJLX(cellValue);
                    continue;
                }
                if (!flag && null != vo.getZKZTDW() && colNum + 1 == vo.getZKZTDW()) {
                    zk.setZKZTDW(cellValue);
                    continue;
                }
                if (!flag && null != vo.getZKGDMC() && colNum + 1 == vo.getZKGDMC()) {
                    zk.setZKGDMC(cellValue);
                    continue;
                }
                if (null != vo.getZKDMBG() && colNum + 1 == vo.getZKDMBG()) {
                    if (isNumber(cellValue)) {
                        zk.setZKDMBG(new BigDecimal(cellValue));
                    }
                    continue;
                }
                if (null != vo.getJYJCGC() && colNum + 1 == vo.getJYJCGC()) {
                    if (isNumber(cellValue)) {
                        zk.setJYJCGC(new BigDecimal(cellValue));
                    }
                    continue;
                }
                if (null != vo.getSORTKEY() && colNum + 1 == vo.getSORTKEY()) {
                    if (isNumber(cellValue)) {
                        zk.setSORTKEY(new Double(cellValue).intValue());
                    }
                    continue;
                }
                if (null != vo.getGCYS() && colNum + 1 == vo.getGCYS()) {
                    if (isNumber(cellValue)) {
                        zk.setGCYS(new Double(cellValue).intValue());
                        tc.setGCYS(new Double(cellValue).intValue());
                        sw.setGCYS(new Double(cellValue).intValue());
                    }
                    continue;
                }
                //土层值设置
                if (colNum + 1 == vo.getTCCDSD()) {
                    if (flag || !isNumber(cellValue)) {
                        failedDataVo.setReason(titleData.get(colNum)+"需为数字");
                        //listFailed.add(failedDataVo);
                       // rowNums++;
                        break;
                    }
                    tc.setTCCDSD(new BigDecimal(cellValue));
                    zk.setTCCDSD_(new BigDecimal(cellValue));
                    zk.setTCCDSD(new BigDecimal(cellValue));
                    continue;
                }
                if (null != vo.getTCXH() && colNum + 1 == vo.getTCXH()) {
                    if (isNumber(cellValue)) {
                        tc.setTCXH(new Double(cellValue).intValue());
                    }
                    continue;
                }
                if (!flag && null != vo.getTCBH() && colNum + 1 == vo.getTCBH()) {
                    tc.setTCBH(cellValue);
                    continue;
                }
                if (null != vo.getTCYCBH() && colNum + 1 == vo.getTCYCBH()) {
                    if (isNumber(cellValue)) {
                        tc.setTCYCBH(Integer.parseInt(cellValue));
                    }
                    continue;
                }
                if (null != vo.getTCCYCBH() && colNum + 1 == vo.getTCCYCBH()) {
                    if (isNumber(cellValue)) {
                        tc.setTCCYCBH(Integer.parseInt(cellValue));
                    }
                    continue;
                }
                if (null != vo.getTCZCBH() && colNum + 1 == vo.getTCZCBH()) {
                    if (isNumber(cellValue)) {
                        tc.setTCZCBH(new Double(cellValue).intValue());
                    }
                    continue;
                }
                if (null != vo.getTCCYCBH() && colNum + 1 == vo.getTCCYCBH()) {
                    if (isNumber(cellValue)) {
                        tc.setTCCYCBH(new Double(cellValue).intValue());
                    }
                    continue;
                }
                if (!flag && null != vo.getTCSDCY() && colNum + 1 == vo.getTCSDCY()) {
                    tc.setTCSDCY(cellValue);
                    continue;
                }
                if (!flag && null != vo.getTCDZSD() && colNum + 1 == vo.getTCDZSD()) {
                    tc.setTCDZSD(cellValue);
                    continue;
                }
                if (!flag && null != vo.getTCSDCY() && colNum + 1 == vo.getTCSDCY()) {
                    tc.setTCSDCY(cellValue);
                    continue;
                }

                if (!flag && null != vo.getTCDZCY() && colNum + 1 == vo.getTCDZCY()) {
                    tc.setTCDZCY(cellValue);
                    continue;
                }

                if (!flag && null != vo.getTCDCHD() && colNum + 1 == vo.getTCDCHD()) {
                    tc.setTCDCHD(cellValue);
                    continue;
                }
                if (colNum + 1 == vo.getTCHD()) {
                    if (flag || !isNumber(cellValue)) {
                        failedDataVo.setReason(titleData.get(colNum)+"需为数字");
                       // listFailed.add(failedDataVo);
                        //rowNums++;
                        break;
                    }
                    zk.setTCHD(new BigDecimal(cellValue));
                    tc.setTCHD(new BigDecimal(cellValue));
                    continue;
                }
                if (!flag && null != vo.getTCLM() && colNum + 1 == vo.getTCLM()) {
                    tc.setTCLM(cellValue);
                    continue;
                }
                if (colNum + 1 == vo.getTCMC()) {
                    if(flag){
                        failedDataVo.setReason("岩层名称不能为空");
                      //  listFailed.add(failedDataVo);
                       // rowNums++;
                        break;
                    }
                    tc.setTCMC(cellValue);
                    zk.setTCMC_(cellValue);
                    zk.setTCMC(cellValue);
                    continue;
                }
                if (!flag && null != vo.getTCMS() && colNum + 1 == vo.getTCMS()) {
                    tc.setTCMS(cellValue);
                    continue;
                }
                //水位值设置
                if (null != vo.getSWSD() && colNum + 1 == vo.getSWSD()) {
                    if (isNumber(cellValue)) {
                        sw.setSWSD(new BigDecimal(cellValue));
                    }
                    continue;
                }
                if (!flag && null != vo.getSWCSRQ() && colNum + 1 == vo.getSWCSRQ()) {
                    sw.setSWCSRQ(cellValue);
                    continue;
                }
                if (null != vo.getSWDXSW() && colNum + 1 == vo.getSWDXSW()) {
                    if (isNumber(cellValue)) {
                        sw.setSWDXSW(new BigDecimal(cellValue));
                    }
                    continue;
                }
                if (null != x && null != y) {
                    BigDecimal lat = BigDecimal.ZERO;
                    BigDecimal lon = BigDecimal.ZERO;
                    if(1 == vo.getIsChange()){
                        lat = XyChangeUtil.change(x.doubleValue(), y.doubleValue(),system.getCoorChangeLat());
                        lon = XyChangeUtil.change(x.doubleValue(), y.doubleValue(),system.getCoorChangeLon());
                    }else{
                        lat = y;//纬度-90->90
                        lon = x;//经度-180->180
                    }
                    if (lon.compareTo(new BigDecimal("180")) > 0 || lon.compareTo(new BigDecimal("-180")) < 0 ||
                            lat.compareTo(new BigDecimal("90")) > 0 || lat.compareTo(new BigDecimal("-90")) < 0) {
                        failedDataVo.setReason("x或y坐标系不在范围内");
                      //  listFailed.add(failedDataVo);
                      //  rowNums++;
                        break;
                    }
                    zk.setZKX(lon);
                    zk.setZKY(lat);
                    zk.setZKOX(x);
                    zk.setZKOY(y);
                }
//                if(null != vo.getGCMC() && !("").equals(vo.getGCMC())  && !("工程名称").equals(vo.getGCMC())&& colNum + 1 == vo.getGCMC()){
//                    zk.setGCMC(cellValue);
//                    continue;
//                }
                //如果工程名称已存在则不进行保存
                if(projects.toString().contains(cellValue)){
                    zk.setGCMC(cellValue);
                    continue;
                }
                if(null != vo.getGCMC() && !("").equals(vo.getGCMC())  && !("工程名称").equals(vo.getGCMC())&& colNum + 1 == vo.getGCMC()){
                    tabProject.setGCMC(cellValue);
                    projects.append(cellValue);
                    continue;
                }
                if(!flag && null != vo.getGCBH() && !("").equals(vo.getGCBH()) && colNum + 1 == vo.getGCBH()){
                    tabProject.setGCBH(cellValue);
                    continue;
                }

                if(!flag && null != vo.getGCRQ() && colNum + 1 == vo.getGCRQ()){
                    tabProject.setGCRQ(cellValue);
                    continue;
                }
                if(!flag && null != vo.getGCFZR() && colNum + 1 == vo.getGCFZR()){
                    tabProject.setGCFZR(cellValue);
                    continue;
                }
            }
            if(null == zk.getZKX() || null == zk.getZKY() || null == tc.getTCCDSD() || null == tc.getTCHD()
                    || null == tc.getTCMC()){
                failedDataVo.setRowIndex(rowNums.toString());
                failedDataVo.setReason(null == failedDataVo.getReason()?"数据缺少必要信息":failedDataVo.getReason());
                listFailed.add(failedDataVo);
                rowNums++;
               continue;
            }
            if (null != zk.getZKX() && null != zk.getZKY()) {
                zk.setDataIndex(rowNums);
                zk.setSettingId(settingId);
                listZk.add(zk);
            }
            if (null != tc.getTCCDSD() && null != tc.getTCHD() && null != tc.getTCMC()) {
                tc.setDataIndex(rowNums);
                tc.setSettingId(settingId);
                listTc.add(tc);
            }
            if (null != sw.getSWSD()) {
                sw.setDataIndex(rowNums);
                sw.setSettingId(settingId);
                listSw.add(sw);
            }
            if(null != tabProject.getGCMC()){
                tabProject.setSettingId(settingId);
                listP.add(tabProject);
            }
            rowNums++;
        }
        Cache cache = cacheManager.getCache("cache-data");
        //存库
//        if(listZk.size()>0){
//            saveDataService.saveBatch(listZk);
//        }
//        if(listTc.size()>0){
//            saveDataService.saveBatch(listTc);
//        }
//        if(listSw.size()>0){
//            saveDataService.saveBatch(listSw);
//        }
        if(listP.size()>0){
            if(null != cache.get(vo.getUuid()+"project")){
                cache.put(vo.getUuid()+"project",listP.get(listP.size()-1));
            }else{
                cache.put(vo.getUuid()+"project",listP.get(listP.size()-1));
            }
        }
//        if(null != cache.get(vo.getUuid()+"cg")){
//            Integer cg = cache.get(vo.getUuid()+"cg",Integer.class);
//            cache.put(vo.getUuid()+"cg",cg+listZk.size());
//        }else{
//            cache.put(vo.getUuid()+"cg",listZk.size());
//        }
        if(null != cache.get(vo.getUuid()+"result")){
            TaskResultVo resultVo = cache.get(vo.getUuid()+"result",TaskResultVo.class);
            List<ExcelInFailedDataVo> list = resultVo.getListFailed();
            list.addAll(listFailed);

            List<TabZuankong> zkList = resultVo.getListZk();
            zkList.addAll(listZk);

            List<TabZkTuceng> tcList = resultVo.getListTc();
            tcList.addAll(listTc);

            List<TabZkShuiwei> swList = resultVo.getListSw();
            swList.addAll(listSw);

            resultVo.setListFailed(list);
            resultVo.setListZk(zkList);
            resultVo.setListTc(tcList);
            resultVo.setListSw(swList);
            cache.put(vo.getUuid()+"result",resultVo);
        }else{
            TaskResultVo resultVo = new TaskResultVo();
            resultVo.setListFailed(listFailed);
            resultVo.setListZk(listZk);
            resultVo.setListSw(listSw);
            resultVo.setListTc(listTc);
            cache.put(vo.getUuid()+"result",resultVo);
        }
    }

    private boolean isNumber(String value) {
        if(null == value){
            return false;
        }
        String matchStr = "^[-\\+]?(\\d+|\\d+\\.\\d+)$";
        return value.matches(matchStr);
    }


}
